
alter session set current_schema = XXL;

/* SET NAMES utf8mb4; */

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE xxl_job_info (
                              id number(10) NOT NULL,
                              job_group number(10) NOT NULL,
                              job_desc varchar2(255 char) NOT NULL,
                              add_time timestamp(0) DEFAULT NULL,
                              update_time timestamp(0) DEFAULT NULL,
                              author varchar2(64 char) DEFAULT NULL,
                              alarm_email varchar2(255 char) DEFAULT NULL,
                              schedule_type varchar2(50 char) DEFAULT 'NONE' NOT NULL,
                              schedule_conf varchar2(128 char) DEFAULT NULL,
                              misfire_strategy varchar2(50 char) DEFAULT 'DO_NOTHING' NOT NULL,
                              executor_route_strategy varchar2(50 char) DEFAULT NULL,
                              executor_handler varchar2(255 char) DEFAULT NULL,
                              executor_param varchar2(512 char) DEFAULT NULL,
                              executor_block_strategy varchar2(50 char) DEFAULT NULL,
                              executor_timeout number(10) DEFAULT '0' NOT NULL,
                              executor_fail_retry_count number(10) DEFAULT '0' NOT NULL,
                              glue_type varchar2(50 char) NOT NULL,
                              glue_source clob,
                              glue_remark varchar2(128 char) DEFAULT NULL,
                              glue_updatetime timestamp(0) DEFAULT NULL,
                              child_jobid varchar2(255 char) DEFAULT NULL,
                              trigger_status number(3) DEFAULT '0' NOT NULL,
                              trigger_last_time number(19) DEFAULT '0' NOT NULL,
                              trigger_next_time number(19) DEFAULT '0' NOT NULL,
                              PRIMARY KEY (id)
) ;

-- Generate ID using sequence and trigger
CREATE SEQUENCE xxl_job_info_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER xxl_job_info_seq_tr
                                                     BEFORE INSERT ON xxl_job_info FOR EACH ROW
 WHEN (NEW.id IS NULL)
BEGIN
SELECT xxl_job_info_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

COMMENT ON COLUMN xxl_job_info.job_group IS '执行器主键ID';
COMMENT ON COLUMN xxl_job_info.author IS '作者';
COMMENT ON COLUMN xxl_job_info.alarm_email IS '报警邮件';
COMMENT ON COLUMN xxl_job_info.schedule_type IS '调度类型';
COMMENT ON COLUMN xxl_job_info.schedule_conf IS '调度配置，值含义取决于调度类型';
COMMENT ON COLUMN xxl_job_info.misfire_strategy IS '调度过期策略';
COMMENT ON COLUMN xxl_job_info.executor_route_strategy IS '执行器路由策略';
COMMENT ON COLUMN xxl_job_info.executor_handler IS '执行器任务handler';
COMMENT ON COLUMN xxl_job_info.executor_param IS '执行器任务参数';
COMMENT ON COLUMN xxl_job_info.executor_block_strategy IS '阻塞处理策略';
COMMENT ON COLUMN xxl_job_info.executor_timeout IS '任务执行超时时间，单位秒';
COMMENT ON COLUMN xxl_job_info.executor_fail_retry_count IS '失败重试次数';
COMMENT ON COLUMN xxl_job_info.glue_type IS 'GLUE类型';
COMMENT ON COLUMN xxl_job_info.glue_source IS 'GLUE源代码';
COMMENT ON COLUMN xxl_job_info.glue_remark IS 'GLUE备注';
COMMENT ON COLUMN xxl_job_info.glue_updatetime IS 'GLUE更新时间';
COMMENT ON COLUMN xxl_job_info.child_jobid IS '子任务ID，多个逗号分隔';
COMMENT ON COLUMN xxl_job_info.trigger_status IS '调度状态：0-停止，1-运行';
COMMENT ON COLUMN xxl_job_info.trigger_last_time IS '上次调度时间';
COMMENT ON COLUMN xxl_job_info.trigger_next_time IS '下次调度时间';

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE xxl_job_log (
                             id number(19) NOT NULL,
                             job_group number(10) NOT NULL,
                             job_id number(10) NOT NULL,
                             executor_address varchar2(255 char) DEFAULT NULL,
                             executor_handler varchar2(255 char) DEFAULT NULL,
                             executor_param varchar2(512 char) DEFAULT NULL,
                             executor_sharding_param varchar2(20 char) DEFAULT NULL,
                             executor_fail_retry_count number(10) DEFAULT '0' NOT NULL,
                             trigger_time timestamp(0) DEFAULT NULL,
                             trigger_code number(10) NOT NULL,
                             trigger_msg clob,
                             handle_time timestamp(0) DEFAULT NULL,
                             handle_code number(10) NOT NULL,
                             handle_msg clob,
                             alarm_status number(3) DEFAULT '0' NOT NULL,
                             PRIMARY KEY (id)
) ;

-- Generate ID using sequence and trigger
CREATE SEQUENCE xxl_job_log_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER xxl_job_log_seq_tr
                                                    BEFORE INSERT ON xxl_job_log FOR EACH ROW
 WHEN (NEW.id IS NULL)
BEGIN
SELECT xxl_job_log_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

COMMENT ON COLUMN xxl_job_log.job_group IS '执行器主键ID';
COMMENT ON COLUMN xxl_job_log.job_id IS '任务，主键ID';
COMMENT ON COLUMN xxl_job_log.executor_address IS '执行器地址，本次执行的地址';
COMMENT ON COLUMN xxl_job_log.executor_handler IS '执行器任务handler';
COMMENT ON COLUMN xxl_job_log.executor_param IS '执行器任务参数';
COMMENT ON COLUMN xxl_job_log.executor_sharding_param IS '执行器任务分片参数，格式如 1/2';
COMMENT ON COLUMN xxl_job_log.executor_fail_retry_count IS '失败重试次数';
COMMENT ON COLUMN xxl_job_log.trigger_time IS '调度-时间';
COMMENT ON COLUMN xxl_job_log.trigger_code IS '调度-结果';
COMMENT ON COLUMN xxl_job_log.trigger_msg IS '调度-日志';
COMMENT ON COLUMN xxl_job_log.handle_time IS '执行-时间';
COMMENT ON COLUMN xxl_job_log.handle_code IS '执行-状态';
COMMENT ON COLUMN xxl_job_log.handle_msg IS '执行-日志';
COMMENT ON COLUMN xxl_job_log.alarm_status IS '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败';

CREATE INDEX I_trigger_time ON xxl_job_log (trigger_time);
CREATE INDEX I_handle_code ON xxl_job_log (handle_code);

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE xxl_job_log_report (
                                    id number(10) NOT NULL,
                                    trigger_day timestamp(0) DEFAULT NULL,
                                    running_count number(10) DEFAULT '0' NOT NULL,
                                    suc_count number(10) DEFAULT '0' NOT NULL,
                                    fail_count number(10) DEFAULT '0' NOT NULL,
                                    update_time timestamp(0) DEFAULT NULL,
                                    PRIMARY KEY (id),
                                    CONSTRAINT i_trigger_day UNIQUE (trigger_day)
) ;

-- Generate ID using sequence and trigger
CREATE SEQUENCE xxl_job_log_report_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER xxl_job_log_report_seq_tr
                                                           BEFORE INSERT ON xxl_job_log_report FOR EACH ROW
 WHEN (NEW.id IS NULL)
BEGIN
SELECT xxl_job_log_report_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

COMMENT ON COLUMN xxl_job_log_report.trigger_day IS '调度-时间';
COMMENT ON COLUMN xxl_job_log_report.running_count IS '运行中-日志数量';
COMMENT ON COLUMN xxl_job_log_report.suc_count IS '执行成功-日志数量';
COMMENT ON COLUMN xxl_job_log_report.fail_count IS '执行失败-日志数量';

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE xxl_job_logglue (
                                 id number(10) NOT NULL,
                                 job_id number(10) NOT NULL,
                                 glue_type varchar2(50 char) DEFAULT NULL,
                                 glue_source clob,
                                 glue_remark varchar2(128 char) NOT NULL,
                                 add_time timestamp(0) DEFAULT NULL,
                                 update_time timestamp(0) DEFAULT NULL,
                                 PRIMARY KEY (id)
) ;

-- Generate ID using sequence and trigger
CREATE SEQUENCE xxl_job_logglue_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER xxl_job_logglue_seq_tr
                                                        BEFORE INSERT ON xxl_job_logglue FOR EACH ROW
 WHEN (NEW.id IS NULL)
BEGIN
SELECT xxl_job_logglue_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

COMMENT ON COLUMN xxl_job_logglue.job_id IS '任务，主键ID';
COMMENT ON COLUMN xxl_job_logglue.glue_type IS 'GLUE类型';
COMMENT ON COLUMN xxl_job_logglue.glue_source IS 'GLUE源代码';
COMMENT ON COLUMN xxl_job_logglue.glue_remark IS 'GLUE备注';

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE xxl_job_registry (
                                  id number(10) NOT NULL,
                                  registry_group varchar2(50 char) NOT NULL,
                                  registry_key varchar2(255 char) NOT NULL,
                                  registry_value varchar2(255 char) NOT NULL,
                                  update_time timestamp(0) DEFAULT NULL,
                                  PRIMARY KEY (id)
) ;

-- Generate ID using sequence and trigger
CREATE SEQUENCE xxl_job_registry_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER xxl_job_registry_seq_tr
                                                         BEFORE INSERT ON xxl_job_registry FOR EACH ROW
 WHEN (NEW.id IS NULL)
BEGIN
SELECT xxl_job_registry_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

CREATE INDEX i_g_k_v ON xxl_job_registry (registry_group,registry_key,registry_value);

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE xxl_job_group (
                               id number(10) NOT NULL,
                               app_name varchar2(64 char) NOT NULL,
                               title varchar2(12 char) NOT NULL,
                               address_type number(3) DEFAULT '0' NOT NULL,
                               address_list clob,
                               update_time timestamp(0) DEFAULT NULL,
                               PRIMARY KEY (id)
) ;

-- Generate ID using sequence and trigger
CREATE SEQUENCE xxl_job_group_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER xxl_job_group_seq_tr
                                                      BEFORE INSERT ON xxl_job_group FOR EACH ROW
 WHEN (NEW.id IS NULL)
BEGIN
SELECT xxl_job_group_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

COMMENT ON COLUMN xxl_job_group.app_name IS '执行器AppName';
COMMENT ON COLUMN xxl_job_group.title IS '执行器名称';
COMMENT ON COLUMN xxl_job_group.address_type IS '执行器地址类型：0=自动注册、1=手动录入';
COMMENT ON COLUMN xxl_job_group.address_list IS '执行器地址列表，多地址逗号分隔';

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE xxl_job_user (
                              id number(10) NOT NULL,
                              username varchar2(50 char) NOT NULL,
                              password varchar2(50 char) NOT NULL,
                              role number(3) NOT NULL,
                              permission varchar2(255 char) DEFAULT NULL,
                              PRIMARY KEY (id),
                              CONSTRAINT i_username UNIQUE (username)
) ;

-- Generate ID using sequence and trigger
CREATE SEQUENCE xxl_job_user_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER xxl_job_user_seq_tr
                                                     BEFORE INSERT ON xxl_job_user FOR EACH ROW
 WHEN (NEW.id IS NULL)
BEGIN
SELECT xxl_job_user_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/

COMMENT ON COLUMN xxl_job_user.username IS '账号';
COMMENT ON COLUMN xxl_job_user.password IS '密码';
COMMENT ON COLUMN xxl_job_user.role IS '角色：0-普通用户、1-管理员';
COMMENT ON COLUMN xxl_job_user.permission IS '权限：执行器ID列表，多个逗号分割';

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE xxl_job_lock (
                              lock_name varchar2(50 char) NOT NULL,
                              PRIMARY KEY (lock_name)
) ;

COMMENT ON COLUMN xxl_job_lock.lock_name IS '锁名称';

-- SQLINES LICENSE FOR EVALUATION USE ONLY
INSERT INTO xxl_job_group(id, app_name, title, address_type, address_list, update_time) VALUES (XXL_JOB_GROUP_SEQ.nextval, 'xxl-job-executor-sample', '示例执行器', 0, NULL, TIMESTAMP '2018-11-03 22:21:31' );
-- SQLINES LICENSE FOR EVALUATION USE ONLY
INSERT INTO xxl_job_info(id, job_group, job_desc, add_time, update_time, author, alarm_email, schedule_type, schedule_conf, misfire_strategy, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid)
VALUES (XXL_JOB_INFO_SEQ.nextval, 1, '测试任务1', TIMESTAMP '2018-11-03 22:21:31', TIMESTAMP '2018-11-03 22:21:31', 'XXL', '', 'CRON', '0 0 0 * * ? *', 'DO_NOTHING', 'FIRST', 'demoJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', TIMESTAMP '2018-11-03 22:21:31', '');
-- SQLINES LICENSE FOR EVALUATION USE ONLY
INSERT INTO xxl_job_user(id, username, password, role, permission) VALUES (XXL_JOB_USER_SEQ.nextval, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);
-- SQLINES LICENSE FOR EVALUATION USE ONLY
INSERT INTO xxl_job_lock ( lock_name) VALUES ( 'schedule_lock');

commit;

